package com.taoism.utils;

import com.taoism.annotation.Excel;
import com.taoism.customEnum.ExcelTypeEnum;
import com.taoism.customEnum.MatchTypeEnum;
import com.taoism.exception.ExcelException;
import org.apache.poi.openxml4j.util.ZipSecureFile;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * Excel导入导出工具
 *
 * @author 李嘉
 * @version 1.0
 * @Description Excel导入导出工具
 * @date 2019-05-08 14:07
 */
public class ExcelUtil {

    /**
     * 忽略需要转换的字段
     */
    private final static String IGNORE_CONVERT = "MATNR,VKORG,KUNAG,KUNNR,YEAR,MONTH";

    /**
     * 导出Excel
     *
     * @param list            数据源
     * @param fieldMap        字段（实体对象对应的字段名称为Key，中文名称为值）
     * @param requiredFildMap 必填字段
     * @param sheetName       sheet名称
     * @param cellWidth       列宽
     * @param outputStream    输出流
     * @param <T>             实体类对象
     * @throws ExcelException
     */
    public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, List<String> requiredFildMap, String sheetName, Integer cellWidth,
                                       OutputStream outputStream) throws ExcelException {
        /*if (list == null || list.size() <= 0) {
            throw new ExcelException("暂无数据！");
        }*/

        Workbook workbook = null;

        try {
            if (list == null || list.size() <= 0) {
                workbook = new XSSFWorkbook();
            } else {
                if (list.get(0) instanceof HashMap) {
                    workbook = new SXSSFWorkbook(1000);
                } else {
                    workbook = new XSSFWorkbook();
                }
            }
            Sheet sheet = workbook.createSheet(sheetName);
            if (requiredFildMap != null && requiredFildMap.size() > 0) {
                List<String> tmp = new ArrayList<>();
                requiredFildMap.forEach((item) -> {
                    tmp.add(item.concat("-Required"));
                });
                requiredFildMap = tmp;
            }
            fillSheet(sheet, list, fieldMap, requiredFildMap, 0, list.size() - 1, cellWidth, workbook);
            workbook.write(outputStream);
        } catch (Exception ex) {
            ex.printStackTrace();
            if (ex instanceof ExcelException) {
                throw (ExcelException) ex;
            } else {
                throw new ExcelException("导出Excel失败！");
            }
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    public static <T> void writeToFile(List<T> list, Class<?> clazz, String sheetName, String filePath, String fileName) throws ExcelException {

        List<String> requiredFieldMap = new ArrayList();
        LinkedHashMap<String, String> fieldMap = toHashMap(clazz, requiredFieldMap, ExcelTypeEnum.EXPORT);
        Workbook workbook = null;
        try {
            if (list != null && list.size() > 0) {
                if (list.get(0) instanceof HashMap) {
                    workbook = new SXSSFWorkbook(1000);
                } else {
                    workbook = new XSSFWorkbook();
                }
            } else {
                workbook = new XSSFWorkbook();
            }

            Sheet sheet = workbook.createSheet(sheetName);
            fillSheet(sheet, list, fieldMap, null, 0, list.size() - 1, 20, workbook);
            File savefile = new File(filePath);
            if (!savefile.exists()) {
                savefile.mkdirs();
            }
            String fileFullPath = filePath + "/" + fileName;
            FileOutputStream fos = new FileOutputStream(fileFullPath);
            workbook.write(fos);

            fos.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            if (ex instanceof ExcelException) {
                throw (ExcelException) ex;
            } else {
                throw new ExcelException("导出Excel失败！");
            }
        } finally {
            try {
                if (workbook != null) {
                    workbook.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 导出Excel
     *
     * @param list         数据源
     * @param fieldMap     字段（实体对象对应的字段名称为Key，中文名称为值）
     * @param sheetName    sheet名称
     * @param outputStream 输出流
     * @param <T>          实体类对象
     * @throws ExcelException
     */
    public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, OutputStream outputStream) throws ExcelException {
        listToExcel(list, fieldMap, null, sheetName, 20, outputStream);
    }

    /**
     * 导出Excel
     *
     * @param list         数据源
     * @param fieldMap     字段（实体对象对应的字段名称为Key，中文名称为值）
     * @param sheetName    sheet名称
     * @param outputStream 输出流
     * @param <T>          实体类对象
     * @throws ExcelException
     */
    public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, List<String> requiredFildMap, String sheetName, OutputStream outputStream) throws ExcelException {
        listToExcel(list, fieldMap, requiredFildMap, sheetName, 20, outputStream);
    }

    /**
     * 导出Excel（浏览器）
     *
     * @param list      数据源
     * @param fieldMap  字段（实体对象对应的字段名称为Key，中文名称为值）
     * @param sheetName sheet名称
     * @param fileName  文件名称
     * @param cellWidth 列宽
     * @param response  请求
     * @param <T>       对象实体
     * @throws ExcelException
     */
    public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, List<String> requiredFildMap, String sheetName, String fileName, Integer cellWidth,
                                       HttpServletResponse response) throws ExcelException {
        listToExcelCommon(list, fieldMap, requiredFildMap, sheetName, fileName, cellWidth, response);
    }

    /**
     * 导出Excel（浏览器）
     *
     * @param list      数据源
     * @param fieldMap  字段（实体对象对应的字段名称为Key，中文名称为值）
     * @param sheetName sheet名称
     * @param fileName  文件名称
     * @param cellWidth 列宽
     * @param response  请求
     * @param <T>       对象实体
     * @throws ExcelException
     */
    public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, String fileName, Integer cellWidth,
                                       HttpServletResponse response) throws ExcelException {
        listToExcelCommon(list, fieldMap, null, sheetName, fileName, cellWidth, response);
    }

    /**
     * 导出公共方法
     *
     * @param list
     * @param fieldMap
     * @param requiredFildMap
     * @param sheetName
     * @param fileName
     * @param cellWidth
     * @param response
     * @param <T>
     * @throws ExcelException
     */
    private static <T> void listToExcelCommon(List<T> list, LinkedHashMap<String, String> fieldMap, List<String> requiredFildMap, String sheetName,
                                              String fileName, Integer cellWidth, HttpServletResponse response) throws ExcelException {
        fileName = fileName == null ? DateUtil.GetFormatTime("yyyyMMddHHmmss") + ".xlsx" : fileName;
        String headStr = "";
        try {
            headStr = "attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"";
        } catch (Exception ex1) {
            headStr = "attachment;filename=\"" + fileName + "\"";
        }

        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-Type", "application/vnd.ms-excel");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("content-disposition", headStr);


        try {
            OutputStream outputStream = response.getOutputStream();
            listToExcel(list, fieldMap, requiredFildMap, sheetName, cellWidth, outputStream);
        } catch (Exception ex) {
            ex.printStackTrace();
            if (ex instanceof ExcelException) {
                throw (ExcelException) ex;
            } else {
                throw new ExcelException("导出Excel失败！");
            }
        }
    }

    /**
     * 导出Excel（浏览器）
     *
     * @param list      数据源
     * @param fieldMap  字段（实体对象对应的字段名称为Key，中文名称为值）
     * @param sheetName sheet名称
     * @param fileName  文件名称
     * @param response  请求
     * @param <T>       对象实体
     * @throws ExcelException
     */
    public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, String sheetName, String fileName, HttpServletResponse response) throws ExcelException {
        listToExcel(list, fieldMap, null, sheetName, fileName, 20, response);
    }

    /**
     * 导出Excel（浏览器）
     *
     * @param list      数据源
     * @param fieldMap  字段（实体对象对应的字段名称为Key，中文名称为值）
     * @param sheetName sheet名称
     * @param fileName  文件名称
     * @param response  请求
     * @param <T>       对象实体
     * @throws ExcelException
     */
    public static <T> void listToExcel(List<T> list, LinkedHashMap<String, String> fieldMap, List<String> requiredFildMap, String sheetName, String fileName, HttpServletResponse response) throws ExcelException {
        listToExcel(list, fieldMap, requiredFildMap, sheetName, fileName, 20, response);
    }

    /**
     * 导出Excel
     *
     * @param list
     * @param clazz
     * @param sheetName
     * @param fileName
     * @param response
     * @param <T>
     * @throws ExcelException
     */
    public static <T> void listToExcel(List<T> list, Class<?> clazz, String sheetName, String fileName, HttpServletResponse response) throws ExcelException {
        List<String> requiredFieldMap = new ArrayList<>();
        LinkedHashMap<String, String> fieldMap = toHashMap(clazz, requiredFieldMap, ExcelTypeEnum.EXPORT);
        listToExcel(list, fieldMap, requiredFieldMap, sheetName, fileName, response);
    }

    /**
     * 导出Excel
     *
     * @param list
     * @param clazz
     * @param sheetName
     * @param fileName
     * @param cellWidth
     * @param response
     * @param <T>
     * @throws ExcelException
     */
    public static <T> void listToExcel(List<T> list, Class<?> clazz, String sheetName, String fileName, Integer cellWidth, HttpServletResponse response) throws ExcelException {
        List<String> requiredFieldMap = new ArrayList<>();
        LinkedHashMap<String, String> fieldMap = toHashMap(clazz, requiredFieldMap, ExcelTypeEnum.EXPORT);
        listToExcel(list, fieldMap, requiredFieldMap, sheetName, fileName, cellWidth, response);
    }

    /**
     * 导入Excel数据
     *
     * @param inputStream 文件输入流
     * @param sheetName   sheet名称
     * @param clazz       实体类对象
     * @param fieldMap    字段（中文名称为Key，实体对象对应的字段名为值）
     * @param <T>         实体对象
     * @return
     * @throws ExcelException
     */
    public static <T> List<T> excelToList(InputStream inputStream, String sheetName, Class<T> clazz, LinkedHashMap<String, String> fieldMap) throws ExcelException {
        List<T> resultList = new ArrayList<>();
        try {
            ZipSecureFile.setMinInflateRatio(-1.0D);
            Workbook workbook = WorkbookFactory.create(inputStream);
            FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
            Sheet sheet;
            if (StringUtil.isEmpty(sheetName)) {
                sheet = workbook.getSheetAt(0);
            } else {
                sheet = workbook.getSheet(sheetName);
            }
            int realRows = 0;
            for (int i = 0; i <= sheet.getLastRowNum(); i++) {
                int nullCells = 0;
                Row row = sheet.getRow(i);

                if (row != null) {
                    for (int j = 0; j < row.getLastCellNum(); ++j) {
                        Cell cell = row.getCell(j);
                        if (cell == null || "".equals(getCellValue(cell, evaluator))) {
                            ++nullCells;
                        }
                    }

                    if (nullCells == row.getLastCellNum()) {
                        break;
                    }

                    ++realRows;
                }
            }

            if (realRows < 1) {
                throw new ExcelException("Excel中暂无数据！");
            }

            Row row = sheet.getRow(0);
            String[] excelFieldNames = new String[row.getLastCellNum()];

            for (int i = 0; i < row.getLastCellNum(); i++) {
                excelFieldNames[i] = row.getCell(i).getStringCellValue().trim();
            }

            boolean isxist = true;
            List<String> excelFieldList = Arrays.asList(excelFieldNames);
            for (String cnName : fieldMap.keySet()) {
                if (!excelFieldList.contains(cnName)) {
                    isxist = false;
                    break;
                }
            }

            if (!isxist) {
                throw new ExcelException("Excel中缺少必要的字段或字段名称错误！");
            }

            LinkedHashMap<String, Integer> cellMap = new LinkedHashMap<>();
            for (int i = 0; i < excelFieldNames.length; i++) {
                cellMap.put(excelFieldNames[i], row.getCell(i).getColumnIndex());
            }

            for (int i = 1; i < realRows; i++) {
                T entity = clazz.newInstance();
                row = sheet.getRow(i);
                for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
                    String cnName = entry.getKey();
                    String enName = entry.getValue();
                    int cellIndex = cellMap.get(cnName);

                    Cell cell = row.getCell(cellIndex);

                    String content = getCellValue(cell, evaluator);

                    setFieldValueByName(enName, content, entity);
                }
                resultList.add(entity);
            }

        } catch (Exception ex) {
            ex.printStackTrace();
            if (ex instanceof ExcelException) {
                throw (ExcelException) ex;
            } else {
                throw new ExcelException("导入Excel失败！");
            }
        }
        return resultList;
    }

    /**
     * 导入Excel数据
     *
     * @param inputStream 文件输入流
     * @param clazz       实体类对象
     * @param fieldMap    字段（中文名称为Key，实体对象对应的字段名为值）
     * @param <T>         实体对象
     * @return
     * @throws ExcelException
     */
    public static <T> List<T> excelToList(InputStream inputStream, Class<T> clazz, LinkedHashMap<String, String> fieldMap) throws ExcelException {
        return excelToList(inputStream, null, clazz, fieldMap);
    }

    /**
     * 导入Excel数据
     *
     * @param inputStream
     * @param clazz
     * @return
     * @throws ExcelException
     */
    public static <T> List<T> excelToList(InputStream inputStream, Class<T> clazz) throws ExcelException {
        return excelToList(inputStream, clazz, toHashMap(clazz, null, ExcelTypeEnum.IMPORT));
    }

    /**
     * 导入Excel数据
     *
     * @param inputStream
     * @param sheetName
     * @param clazz
     * @return
     * @throws ExcelException
     */
    public static <T> List<T> excelToList(InputStream inputStream, String sheetName, Class<T> clazz) throws ExcelException {
        return excelToList(inputStream, sheetName, clazz, toHashMap(clazz, null, ExcelTypeEnum.IMPORT));
    }

    /**
     * 导入Excel数据
     *
     * @param file
     * @param clazz
     * @return
     * @throws ExcelException, IOException
     */
    public static <T> List<T> excelToList(MultipartFile file, Class<T> clazz) throws ExcelException, IOException {
        String fileName = file.getOriginalFilename();
        if (MatcheUtil.matchExcel(fileName, MatchTypeEnum.EXCEL)) {
            throw new ExcelException("上传文件格式不正确！");
        }
        return excelToList(file.getInputStream(), clazz, toHashMap(clazz, null, ExcelTypeEnum.IMPORT));
    }

    /**
     * 导入Excel数据
     *
     * @param file
     * @param sheetName
     * @param clazz
     * @return
     * @throws ExcelException, IOException
     */
    public static <T> List<T> excelToList(MultipartFile file, String sheetName, Class<T> clazz) throws ExcelException, IOException {
        String fileName = file.getOriginalFilename();
        if (MatcheUtil.matchExcel(fileName, MatchTypeEnum.EXCEL)) {
            throw new ExcelException("上传文件格式不正确！");
        }
        return excelToList(file.getInputStream(), sheetName, clazz, toHashMap(clazz, null, ExcelTypeEnum.IMPORT));
    }

    /**
     * 通过反射获取Excel字段
     *
     * @param clazz
     * @param excelTypeEnum
     * @return
     */
    private static LinkedHashMap<String, String> toHashMap(Class<?> clazz, List<String> requiredFildMap, ExcelTypeEnum excelTypeEnum) {
        LinkedHashMap<String, String> linkedHashMap = new LinkedHashMap<>();

        Field[] fields = clazz.getDeclaredFields();

        List<Excel> list = new ArrayList<>();
        Map<String, String> map = new HashMap<>();

        for (Field field : fields) {
            Excel excel = field.getDeclaredAnnotation(Excel.class);
            if (excel != null) {
                map.put(excel.name(), field.getName());
                list.add(excel);
            }
        }

        list.stream().sorted(Comparator.comparing(Excel::order)).forEach(t -> {
            String fieldName = map.get(t.name());
            if (excelTypeEnum == ExcelTypeEnum.IMPORT) {
                linkedHashMap.put(t.name(), fieldName);
            } else {
                if (requiredFildMap != null && t.required()) {
                    requiredFildMap.add(t.name());
                }
                linkedHashMap.put(fieldName, t.name());
            }
        });

        return linkedHashMap;
    }

    /**
     * 根据字段名称获取字段值
     *
     * @param fieldName 字段名称
     * @param object    类对象
     * @return
     * @throws Exception
     */
    private static Object getFieldValueByName(String fieldName, Object object) throws Exception {
        Object value = null;
        Field field = getFieldByName(fieldName, object.getClass());

        if (field != null) {
            field.setAccessible(true);
            value = field.get(object);
        } else {
            throw new ExcelException(object.getClass().getSimpleName() + "类中不存在字段名" + fieldName);
        }
        return value;
    }

    /**
     * 根据字段名称获取类中的字段数据
     *
     * @param fieldName 字段名称
     * @param clazz     字段类
     * @return
     */
    private static Field getFieldByName(String fieldName, Class<?> clazz) {
        Field[] fields = clazz.getDeclaredFields();

        for (Field field : fields) {
            if (field.getName().equalsIgnoreCase(fieldName)) {
                return field;
            }
        }

        Class<?> superClazz = clazz.getSuperclass();
        if (superClazz != null && superClazz != Object.class) {
            return getFieldByName(fieldName, superClazz);
        }

        return null;
    }

    /**
     * 根据带路径或不带路径的属性名获取属性值
     *
     * @param filedNameSequence 带路径或不带路径的属性值
     * @param object            类对象
     * @return
     * @throws Exception
     */
    private static Object getFieldValueByNameSequence(String filedNameSequence, Object object) throws Exception {
        Object value = null;

        String[] attributs = filedNameSequence.split("\\.");
        if (attributs.length == 1) {
            value = getFieldValueByName(filedNameSequence, object);
        } else {
            Object fieldObj = getFieldValueByName(attributs[0], object);
            String subFieldNameSequence = filedNameSequence.substring(filedNameSequence.indexOf(".") + 1);
            value = getFieldValueByNameSequence(subFieldNameSequence, fieldObj);
        }
        return value;
    }

    /**
     * 根据字段名称设置字段的值
     *
     * @param fieldName  字段名称
     * @param fieldValue 字段值
     * @param object     类对象
     * @throws Exception
     */
    private static void setFieldValueByName(String fieldName, Object fieldValue, Object object) throws Exception {
        Field field = getFieldByName(fieldName, object.getClass());
        if (field != null) {
            field.setAccessible(true);
            Class<?> fieldType = field.getType();
            if (fieldType == String.class) {
                try {
                    //判断字符串数值是否带有.0小数
                    if (String.valueOf(fieldValue).contains(".0")) {
                        field.set(object, String.valueOf(Double.valueOf(String.valueOf(fieldValue)).intValue()));
                    } else {
                        field.set(object, String.valueOf(fieldValue));
                    }
                } catch (Exception ex) {
                    field.set(object, String.valueOf(fieldValue));
                }
            } else if (fieldType == Integer.class || fieldType == Integer.TYPE) {
                if (!StringUtil.isEmpty(fieldValue.toString().trim())) {
                    field.set(object, Integer.parseInt(fieldValue.toString().trim()));
                } else {
                    field.set(object, 0);
                }
            } else if (fieldType == Long.class || fieldType == Long.TYPE) {
                if (!StringUtil.isEmpty(fieldValue.toString().trim())) {
                    field.set(object, Long.valueOf(fieldValue.toString().trim()));
                } else {
                    field.set(object, Long.valueOf("0"));
                }
            } else if (fieldType == Float.class || fieldType == Float.TYPE) {
                if (!StringUtil.isEmpty(fieldValue.toString().trim())) {
                    field.set(object, Float.valueOf(fieldValue.toString().trim()));
                } else {
                    field.set(object, (float) 0);
                }
            } else if (fieldType == Short.class || fieldType == Short.TYPE) {
                if (!StringUtil.isEmpty(fieldValue.toString().trim())) {
                    field.set(object, Short.valueOf(fieldValue.toString().trim()));
                } else {
                    field.set(object, Short.valueOf("0"));
                }
            } else if (fieldType == Double.class || fieldType == Double.TYPE) {
                if (!StringUtil.isEmpty(fieldValue.toString().trim())) {
                    field.set(object, Double.valueOf(fieldValue.toString().trim()));
                } else {
                    field.set(object, (double) 0);
                }
            } else if (fieldType == Character.class || fieldType == Character.TYPE) {
                if (fieldValue != null && fieldValue.toString().trim().length() > 0) {
                    field.set(object, fieldValue.toString().trim().charAt(0));
                }
            } else if (fieldType == Date.class) {
                if (!StringUtil.isEmpty(fieldValue.toString().trim())) {
                    field.set(object, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(fieldValue.toString().trim()));
                } else {
                    field.set(object, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").parse(DateUtil.GetFormatTime()));
                }
            } else if (fieldType == BigDecimal.class) {
                if (StringUtil.isEmpty(fieldValue.toString().trim())) {
                    field.set(object, new BigDecimal(0));
                } else {
                    field.set(object, new BigDecimal(fieldValue.toString().trim()));
                }
            } else {
                field.set(object, fieldValue);
            }
        } else {
            throw new ExcelException(object.getClass().getSimpleName() + "类中不存在字段名" + fieldName);
        }
    }

    /**
     * 设置自动列宽
     *
     * @param sheet
     * @param width
     */
    private static void setColumnAutoSize(Sheet sheet, Integer width) {
        Row row = sheet.getRow(0);
        for (int i = 0; i < row.getLastCellNum(); i++) {
            //sheet.setColumnWidth(i, width * 256);
            sheet.autoSizeColumn(i);
        }
    }

    // 自适应宽度(中文支持)
    private static void setSizeColumn(Sheet sheet, int size) {
        for (int columnNum = 0; columnNum < size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == CellType.STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            int colWidth = columnWidth * 256;
            if (colWidth < 255 * 256) {
                sheet.setColumnWidth(columnNum, Math.max(colWidth, 3000));
            } else {
                sheet.setColumnWidth(columnNum, 6000);
            }
        }
    }

    /**
     * 设置自动列宽
     *
     * @param sheet
     * @param headerLength
     * @param width
     */
    private static void setColumnAutoSize(Sheet sheet, int headerLength, int width) {
        for (int i = 0; i < headerLength; i++) {
            sheet.autoSizeColumn(i);
            sheet.setColumnWidth(i, sheet.getColumnWidth(i) * width / 10);
        }
    }

    /**
     * 设置字段列类型
     *
     * @param cell
     * @param fieldName
     * @param object
     */
    private static void setColumnType(Cell cell, CellStyle cellStyle, String fieldName, Object object) throws Exception {

        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderRight(BorderStyle.THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        cellStyle.setBorderTop(BorderStyle.THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
        cell.setCellStyle(cellStyle);

        if (object instanceof HashMap) {
            HashMap tmpMap = (HashMap) object;
            if (StringUtil.isEmpty(tmpMap.get(fieldName))) {
                cell.setCellValue("");
            } else {
                String val = tmpMap.get(fieldName).toString();
                if (CustomUtil.isDecimal(val) && !IGNORE_CONVERT.contains(fieldName.toUpperCase())) {
                    // cell.setCellType(CellType.NUMERIC);
                    cell.setCellStyle(cellStyle);
                    cell.setCellValue(Double.parseDouble(val));
                } else {
                    // cell.setCellType(CellType.STRING);
                    cell.setCellValue(val);
                }
            }
        } else {
            Object objectValue = getFieldValueByNameSequence(fieldName, object);
            String fieldValue = objectValue == null ? "" : objectValue.toString();

            Field field = getFieldByName(fieldName, object.getClass());

            if (field != null) {
                Class<?> fieldType = field.getType();
                if ((fieldType == Integer.class || fieldType == Integer.TYPE)
                        || (fieldType == Long.class || fieldType == Long.TYPE)
                        || (fieldType == Float.class || fieldType == Float.TYPE)
                        || (fieldType == Short.class || fieldType == Short.TYPE)
                        || (fieldType == Double.class || fieldType == Double.TYPE)
                        || (fieldType == BigDecimal.class)) {
                    // cell.setCellType(CellType.NUMERIC);
                    if ((fieldType == Double.class || fieldType == Double.TYPE)
                            || (fieldType == BigDecimal.class)) {
                        if (!StringUtil.isEmpty(fieldValue)) {
                            cell.setCellStyle(cellStyle);
                            cell.setCellValue(Double.parseDouble(fieldValue));
                        } else {
                            cell.setCellValue(fieldValue);
                        }
                    } else {
                        cell.setCellValue(fieldValue);
                    }
                } else {
                    if (fieldValue.startsWith("@") && fieldValue.endsWith("@")) {
                        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        cellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
                    } else {
                        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        cellStyle.setFillForegroundColor(IndexedColors.WHITE.getIndex());
                    }
                    // cell.setCellType(CellType.STRING);
                    cell.setCellValue(fieldValue);
                }
            }
        }
    }

    /**
     * 填充数据
     *
     * @param sheet      Excel中的Sheet页
     * @param list       数据源
     * @param fieldMap   字段
     * @param firstIndex 开始索引数
     * @param lastIndex  最后索引数
     * @param cellWidth  列宽
     * @param <T>        实体对象
     * @throws Exception
     */
    private static <T> void fillSheet(Sheet sheet, List<T> list, LinkedHashMap<String, String> fieldMap, List<String> requiredFildMap, Integer firstIndex, Integer lastIndex,
                                      Integer cellWidth, Workbook workbook) throws Exception {
        String[] enFields = new String[fieldMap.size()];
        String[] cnFields = new String[fieldMap.size()];

        int count = 0;
        for (Map.Entry<String, String> entry : fieldMap.entrySet()) {
            enFields[count] = entry.getKey();
            cnFields[count] = entry.getValue();
            count++;
        }

        Row row = sheet.createRow(0);
        row.setHeightInPoints(30);
        Cell cell;
        CellStyle style = null;
        boolean flag = false;

        Font font = null;


        for (int i = 0; i < cnFields.length; i++) {
            cell = row.createCell(i);
            cell.setCellValue(cnFields[i]);

            style = workbook.createCellStyle();
            font = workbook.createFont();

            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);
            style.setBorderTop(BorderStyle.THIN);

            if (requiredFildMap != null && requiredFildMap.size() > 0) {
                if (requiredFildMap.contains(cnFields[i].concat("-Required"))) {
                    font.setColor(IndexedColors.WHITE.getIndex());
                    style.setFillForegroundColor(IndexedColors.RED.getIndex());
                    style.setFont(font);
                    flag = true;
                } else {
                    flag = false;
                }
            } else {
                flag = false;
            }
            if (!flag) {
                font.setColor(IndexedColors.BLACK.getIndex());
                style.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
                style.setFont(font);
                flag = true;
            }
            cell.setCellStyle(style);
        }

        int rowIndex = 1;
        if (list != null && list.size() > 0) {
            // 修复填充数据时，循环创建样式序列引发的问题
            CellStyle cellStyle = workbook.createCellStyle();
            DataFormat dataFormat = workbook.createDataFormat();
            cellStyle.setDataFormat(dataFormat.getFormat("#,##0.00"));

            for (int index = firstIndex; index <= lastIndex; index++) {
                T item = list.get(index);
                row = sheet.createRow(rowIndex);
                for (int j = 0; j < enFields.length; j++) {
                    cell = row.createCell(j);
                    setColumnType(cell, cellStyle, enFields[j], item);
                }
                rowIndex++;
            }
        }

        /*setColumnAutoSize(sheet, enFields.length, cellWidth);*/
        if (list == null || list.size() <= 0 || !(list.get(0) instanceof HashMap)) {
            setColumnAutoSize(sheet, cellWidth);
            setSizeColumn(sheet, enFields.length);
        }
    }

    /**
     * 填充数据
     *
     * @param sheet      Excel中的Sheet页
     * @param list       数据源
     * @param fieldMap   字段
     * @param firstIndex 开始索引数
     * @param lastIndex  最后索引数
     * @param <T>        实体对象
     * @throws Exception
     */
    private static <T> void fillSheet(XSSFSheet sheet, List<T> list, LinkedHashMap<String, String> fieldMap, Integer firstIndex, Integer lastIndex) throws Exception {
        fillSheet(sheet, list, fieldMap, null, firstIndex, lastIndex, 20, null);
    }

    /**
     * 获取列值
     *
     * @param cell
     * @return
     */
    private static String getCellValue(Cell cell, FormulaEvaluator evaluator) {
        String content = "";
        if (cell != null) {
            CellType cellType = cell.getCellType();
            switch (cellType) {
                case ERROR:
                    content = String.valueOf(cell.getErrorCellValue());
                    break;
                case BOOLEAN:
                    content = String.valueOf(cell.getBooleanCellValue());
                    break;
                case FORMULA:
                    content = getFormulaValue(cell, evaluator);
                    break;
                case NUMERIC:
                    content = String.valueOf(cell.getNumericCellValue());
                    break;
                default:
                    content = cell.getStringCellValue();
                    break;
            }
        }
        return content;
    }

    /**
     * 获取公式中的值
     *
     * @param cell
     * @param evaluator
     * @return
     */
    private static String getFormulaValue(Cell cell, FormulaEvaluator evaluator) {
        CellValue cellValue = evaluator.evaluate(cell);
        String content = "";
        switch (cellValue.getCellType()) {
            case NUMERIC:
                content = String.valueOf(cellValue.getNumberValue());
                break;
            case BOOLEAN:
                content = String.valueOf(cell.getBooleanCellValue());
                break;
            case ERROR:
                content = String.valueOf(cellValue.getErrorValue());
                break;
            default:
                content = cellValue.getStringValue();
                break;
        }
        return content;
    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("content-Type", "application/vnd.ms-excel");
            response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
            response.setHeader("content-disposition",
                    "attachment;filename=\"" + URLEncoder.encode(fileName, "UTF-8") + "\"");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }



}
